# Extra Features

## Named placeholders

You can use named placeholders for parameters by setting `namedPlaceholders` config value or query/execute time option. Named placeholders are converted to unnamed `?` on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times. Unnamed placeholders can still be used by providing the values as an array instead of an object.

```js
connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => {
  // statement prepared as "select ? + ? as z" and executed with [1,2] values
  // rows returned: [ { z: 3 } ]
});

connection.execute('select :x + :x as z', { x: 1 }, (err, rows) => {
  // select ? + ? as z, execute with [1, 1]
});

connection.query('select :x + :x as z', { x: 1 }, (err, rows) => {
  // query select 1 + 1 as z
});

// unnamed placeholders are still valid if the values are provided in an array
connection.query('select ? + ? as z', [1, 1], (err, rows) => {
  // query select 1 + 1 as z
});
```

## Receiving rows as array of columns instead of hash with column name as key:

```js
const options = { sql: 'select A,B,C,D from foo', rowsAsArray: true };
connection.query(options, (err, results) => {
  /* results will be an array of arrays like this now:
  [[
     'field A value',
     'field B value',
     'field C value',
     'field D value',
  ], ...]
  */
});
```

## Sending tabular data with 'load infile' and local stream:

In addition to sending local fs files you can send any stream using `infileStreamFactory` query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter.

Note: starting from version 2.0 `infileStreamFactory` is required parameter for `LOAD DATA LOCAL INFILE`. Response from server indicates that it wants access to a local file and no `infileStreamFactory` option is provided the query ends with error.

```js
// local file
connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)',
  onInserted1
);
// local stream
const sql =
  'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)';
connection.query(
  {
    sql: sql,
    infileStreamFactory: function (path) {
      return getStream();
    },
  },
  onInserted2
);
```

The `infileStreamFactory` option may also be set at a connection-level:

```js
const fs = require('fs');
const mysql = require('mysql2');

const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  infileStreamFactory: (path) => {
    // Validate file path
    const validPaths = ['/tmp/data.csv'];
    if (!validPaths.includes(path)) {
      throw new Error(
        `invalid file path: ${path}: expected to be one of ${validPaths.join(
          ','
        )}`
      );
    }
    return fs.createReadStream(path);
  },
});

connection.query(
  'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test',
  onInserted
);
```

## Connecting using custom stream:

```js
const net = require('net');
const mysql = require('mysql2');
const shape = require('shaper');
const connection = mysql.createConnection({
  user: 'test',
  database: 'test',
  stream: net.connect('/tmp/mysql.sock').pipe(shape(10)), // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);
```

`stream` also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Below is an updated SOCKS5 proxy example using the **modern API**, where `stream` returns a stream or a Promise resolving to a stream.

```js
const mysql = require('mysql2');
const { SocksClient } = require('socks');

const pool = mysql.createPool({
  database: 'test',
  user: 'foo',
  password: 'bar',

  // modern usage: stream returns a Duplex or a Promise that resolves to one
  stream: async () => {
    const { socket } = await SocksClient.createConnection({
      proxy: {
        host: 'localhost',
        port: 1080,
        type: 5,
      },
      destination: {
        host: 'remote.host',
        port: 3306,
      },
    });
    return socket; // must be a Duplex stream
  },
});
```

**Note:**

Older documentation used a callback-style API:

```js
stream: function (cb) {
  cb(null, stream);
}
```

This callback style no longer works on current versions of mysql2 and causes:

```
TypeError: cb is not a function
```

Use the modern return-style function shown above.

## In addition to password `createConnection()`, `createPool()` and `changeUser()` accept `passwordSha1` option.

This is useful when implementing proxies as plaintext password might be not available.

## Graceful pool connection end

```js
const pool = mysql.createPool({
  gracefulEnd: true,
});
```

By default, calling end on a pool connection will result in a warning being emitted and the connection being released back to the pool. This is for compatibility with mysqljs/mysql.
By providing `gracefulEnd: true` when creating the pool, calling end on a pooled connection will actually close the connection instead of releasing it back to the pool.
